package com.sparrow.common.excel;

import com.sparrow.common.excel.bean.*;
import com.sparrow.common.excel.handler.SheetDataHandler;
import org.apache.commons.compress.archivers.zip.ZipFile;
import org.apache.commons.compress.utils.SeekableInMemoryByteChannel;
import org.apache.commons.io.IOUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.util.ZipFileZipEntrySource;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.util.XMLHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.xml.sax.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.function.Consumer;

/**
 * 基于apache poi XSSF 实现的excel reader
 */
public class ExcelReader {

    public void read(String filename, Consumer<Excel> consumer) {
        OPCPackage pkg = null;
        try {
            pkg = createOpcPackage(filename);
            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
            XSSFReader xssfReader = new XSSFReader(pkg);
            StylesTable styles = xssfReader.getStylesTable();

            Excel excel = new Excel();
            XSSFReader.SheetIterator it = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
            while (it.hasNext()) {
                try (InputStream stream = it.next()) {
                    ExcelSheet sheet = new ExcelSheet();
                    sheet.setSheetName(it.getSheetName());
                    excel.getSheetList().add(sheet);
                    processSheet(sheet, styles, strings, stream);
                }
            }
            consumer.accept(excel);
        } catch (Exception e) {
            throw new RuntimeException("read fileName=" + filename + " 出错！", e);
        } finally {
            if (pkg != null) {
                try {
                    pkg.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    private OPCPackage createOpcPackage(String filename) throws IOException, InvalidFormatException {
        byte[] bytes = IOUtils.toByteArray(new FileInputStream(filename));
        SeekableInMemoryByteChannel byteChannel = new SeekableInMemoryByteChannel(bytes);
        ZipFile zipFile = new ZipFile(byteChannel);
        ZipFileZipEntrySource zipSource = new ZipFileZipEntrySource(zipFile);
        OPCPackage pkg = OPCPackage.open(zipSource);
        return pkg;
    }

    private void processSheet(ExcelSheet sheet, StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) {
        DataFormatter formatter = new DataFormatter(true);
        InputSource sheetSource = new InputSource(sheetInputStream);
        try {
            SheetDataHandler sheetDataHandler = new SheetDataHandler(sheet);
            XMLReader sheetParser = XMLHelper.newXMLReader();
            ContentHandler handler = new XSSFSheetXMLHandler(
                    styles, null, strings, sheetDataHandler, formatter, false);
            sheetParser.setContentHandler(handler);
            sheetParser.parse(sheetSource);
        } catch (Exception e) {
            throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
        }
    }

}
